package webapi

import (
	"bytes"
	"go-web/config"
	"go-web/logutils"
	"go-web/utils"
	dbutils "go-web/utils/db"
	admin "go-web/web-api/admin"
	"net/http"
	"strconv"
	"strings"
	"time"
	"unicode"

	"github.com/jmoiron/sqlx"
)

func ExecSQL(w http.ResponseWriter, r *http.Request) {

	r.ParseForm()
	connId := r.Form.Get("connId")
	schema := r.Form.Get("schema")
	tableName := r.Form.Get("tableName")
	sqlStr := r.Form.Get("sql")
	maxLine := r.Form.Get("maxLine")
	sqlStr = strings.TrimSpace(sqlStr)

	authorization := r.Header.Get("Authorization")
	conn := admin.GetConn(connId, authorization)
	user := admin.GetUser(authorization)

	blankIdx := strings.Index(sqlStr, " ")
	nlIdx := strings.Index(sqlStr, "\n")
	if nlIdx == -1 {
		nlIdx = len(sqlStr)
	}

	if checkPrefx(sqlStr, []string{"update", "delete"}) {
		backup(sqlStr, user, connId, conn)
	} else {
		recordHistory(sqlStr, user, connId)
	}

	// 关键字转小写 select delete update
	sqlStr = strings.Join([]string{sqlStr[0:min(blankIdx, nlIdx)], sqlStr[min(blankIdx, nlIdx):]}, "")

	if checkPrefx(sqlStr, []string{"update", "delete", "alter", "drop ", "insert", "create"}) {
		rspData := TableDataList{Columns: []Column{{Name: "受影响行数", Type: "VARCHAR(10)"}}}
		rspData.Data = batchExec(&sqlStr, conn)
		utils.WriteJson(w, rspData)
	} else {
		params := make([]any, 0)
		if checkPrefx(sqlStr, []string{"select"}) && !checkContains(sqlStr, []string{" limit ", " LIMIT ", "\nlimit\n", "\nLIMIT\n"}) {
			sqlStr = *page(conn.DriverName(), &sqlStr)
			maxLineI, _ := strconv.Atoi(maxLine)
			params = append(params, maxLineI)
		}
		rows, err2 := conn.Queryx(sqlStr, params...)

		if err2 != nil {
			logutils.PanicErr(err2)
		}
		cts, err3 := rows.ColumnTypes()
		logutils.PanicErr(err3)
		columnList := make([]Column, len(cts))
		columnNameList := make([]string, 0)

		var realTableName, realSchema = tableName, schema
		if strings.Contains(tableName, ".") {
			realTableName = string(tableName[strings.Index(tableName, ".")+1:])
			realSchema = string(tableName[0:strings.Index(tableName, ".")])
		}
		var keyIdx []int
		var keys []string
		columnMap := map[string]string{}
		if IsAlphaNumeric(realTableName) && strings.Index(sqlStr, " from ") == strings.LastIndex(sqlStr, " from ") {

			columnMap = admin.ColumnMap(strings.ToLower(realTableName), strings.ToLower(realSchema), conn)

			tx, _ := conn.Beginx()
			defer tx.Rollback()
			var err error
			keys, err = admin.QueryPrimaryKey(schema, realTableName, tx)
			logutils.PrintErr(err)
		}

		for idx, val := range cts {
			columnNameList = append(columnNameList, val.Name())
			columnList[idx] = Column{Name: val.Name(), Type: val.DatabaseTypeName(), Comment: columnMap[val.Name()]}
		}

		if len(keys) != 0 {
			keyIdx = dbutils.KeyIdx(keys, columnNameList)
		}

		data := dbutils.GetResultRows(conn.DriverName(), rows)

		rspData := &TableDataList{Columns: columnList, Data: data, CanEdit: len(keyIdx) != 0, Keys: keys}

		utils.WriteJson(w, rspData)
	}
}

// 判断字符串是否为字母数字
func IsAlphaNumeric(str string) bool {
	// 遍历字符串，判断每个字符是否为字母数字
	for _, ch := range str {
		if unicode.IsLetter(ch) || unicode.IsDigit(ch) {
			return true
		}
	}
	return false
}

func min(a, b int) int {
	if a > b {
		return a
	}
	return b
}

func page(dbtype string, sql *string) *string {
	pageSql := ""
	if dbtype == "oracle" {
		pageSql = "select a.* from (" + *sql + ") a where rownum <= :1"
	} else if dbtype == "mysql" {
		pageSql = *sql + " limit ?"
	}
	return &pageSql
}

func batchExec(sql *string, db *sqlx.DB) []map[string]any {
	sqlArr := strings.Split(*sql, ";")
	tx, err := db.Beginx()
	defer tx.Rollback()
	logutils.PanicErrf("事务开启失败， %s", err)
	resultData := []map[string]any{}
	mgntTx, _ := config.Mngtdb.Beginx()
	defer mgntTx.Rollback()
	for idx := range sqlArr {
		sqlStr := strings.TrimSpace(sqlArr[idx])
		if sqlStr == "" {
			continue
		}
		rs, err2 := tx.Exec(sqlStr)
		logutils.PanicErr(err2)
		affected, err := rs.RowsAffected()
		logutils.PanicErr(err)
		resultData = append(resultData, map[string]any{"受影响行数": affected})
	}
	err = mgntTx.Commit()
	logutils.PanicErr(err)
	err = tx.Commit()
	logutils.PanicErr(err)
	return resultData
}

func backup(ddlSql string, user *admin.User, connId string, conn *sqlx.DB) {
	operationType := ""
	backupSql := bytes.NewBufferString("select * from ")
	if strings.HasPrefix(ddlSql, "update ") {
		operationType = "update"
		tmp := strings.TrimSpace(strings.TrimPrefix(ddlSql, "update "))
		backupSql.WriteString(tmp[:strings.Index(tmp, " ")])
	} else if strings.HasPrefix(ddlSql, "delete ") {
		operationType = "delete"
		tmp := strings.TrimPrefix(strings.TrimSpace(strings.TrimPrefix(ddlSql, "delete ")), "from ")
		backupSql.WriteString(tmp[:strings.Index(tmp, " ")])
	}
	backupSql.WriteString(ddlSql[strings.Index(ddlSql, " where "):])
	rows, err := conn.Queryx(backupSql.String())
	logutils.PanicErr(err)
	data := dbutils.GetResultRows(conn.DriverName(), rows)
	backupInsertSql := "insert into t_history (id,user,conn_id,operation_type,exec_time,exec_sql,data) values(?,?,?,?,?,?,?)"
	stmt, err := config.Mngtdb.Preparex(backupInsertSql)
	logutils.PanicErr(err)
	time.Sleep(time.Microsecond)
	_, err3 := stmt.Exec(time.Now().UnixMicro(), user.LoginName, connId, operationType, time.Now(), ddlSql, string(utils.ToJsonString(data)))
	logutils.PanicErr(err3)
}

func recordHistory(ddlSql string, user *admin.User, connId string) {
	backupInsertSql := "insert into t_history (id,user,conn_id,operation_type,exec_time,exec_sql) values(?,?,?,?,?,?)"
	stmt, err := config.Mngtdb.Preparex(backupInsertSql)
	logutils.PanicErr(err)
	time.Sleep(time.Microsecond)
	_, err3 := stmt.Exec(time.Now().UnixMicro(), user.LoginName, connId, "select", time.Now(), ddlSql)
	logutils.PanicErr(err3)
}

func checkPrefx(src string, prefix []string) bool {
	src = strings.ToUpper(src)
	for _, p := range prefix {
		p = strings.ToUpper(p)
		if strings.HasPrefix(src, p+" ") || strings.HasPrefix(src, p+"\n") {
			return true
		}
	}
	return false
}

func checkContains(src string, suffix []string) bool {
	for _, p := range suffix {
		if strings.LastIndex(src, p) != -1 {
			return true
		}
	}
	return false
}

type Column struct {
	Name    string `json:"name"`
	Type    string `json:"type"`
	Comment string `json:"comment"`
}

type TableDataList struct {
	Columns []Column                 `json:"columns"`
	Data    []map[string]interface{} `json:"data"`
	CanEdit bool                     `json:"canEdit"`
	Keys    []string                 `json:"keys"`
}
